import requests,datetime,xlwings,sys,os,json,time,random,uuid
import pandas,psycopg2
from sqlalchemy import create_engine

engine = create_engine('postgresql://postgres:postgres@localhost:5432/gisdb')

def execute_sql(db, sql, bool):
	try:
		conn = psycopg2.connect(host=db['host'], user=db['user'], password=db['password'], database=db['database'], port=db['port'])
		cur = conn.cursor()
		cur.execute(sql)
		if bool:
			data = cur.fetchall()
			conn.commit()
			cur.close()
			conn.close()
			return data
		else:
			conn.commit()
			cur.close()
			conn.close()
	except Exception as err:
		print('execute_sql err: ', err)

def httpOp(req):
	i = 0
	while i < 3:
		try:
			d = None
			url, headersList, bodydict, method = req['url'], req['headersList'], req['bodydict'], req['method']
			res = requests.request(method, url, data=json.dumps(bodydict), headers=headersList)
			if res.status_code == 200:
				d = json.loads(res.text)
				if d['status'] != 'OK':
					print(d)
					print(bodydict)
					return False
				return d
			else:
				i += 1
				res.close()
				print(res.status_code, url)
		except requests.exceptions.RequestException:
			i += 1
	return False

def main():
	try:
		db = {'host': 'localhost', 'user': 'postgres', 'password': 'postgres', 'database': 'gisdb', 'port': '5432'}
		gtyinfo1 = {
			"method": "POST",
			"url": "https://www.landcloud.org.cn/landCloudWork3/taskManager/queryTaskDataForListDisplay",
			"headersList": {
				"Accept": "*/*",
				"User-Agent": "Thunder Client (https://www.thunderclient.com)",
				"Cookie": "",
				"Logintoken": "",
				"Content-Type": "application/json"
			},
			"bodydict": {
				"page": 1,
				"rows": 50000,
				"taskId": "a4a00eab-ba23-4af0-ac11-9e6aadc465ab",
				"keyWord": "YN_HF",
				"likeStatus": 3,
				"customFilter": "[{\"ref\":\"IS\",\"fieldName\":\"f_shape\",\"val\":\"NOT NULL\"}]",
				"regionCode": "522300",
				"sortParam": "f_committime desc nulls last"
			}
		}
		
		gtyinfo2 = {
			"method": "POST",
			"url": "https://www.landcloud.org.cn/landCloudWork3/taskManager/queryTaskDataForListDisplay",
			"headersList": {
				"Accept": "*/*",
				"User-Agent": "Thunder Client (https://www.thunderclient.com)",
				"Cookie": "",
				"Logintoken": "",
				"Content-Type": "application/json"
			},
			"bodydict": {
				"page": 1,
				"rows": 50000,
				"taskId": "a4a00eab-ba23-4af0-ac11-9e6aadc465ab",
				"keyWord": "YNHF",
				"likeStatus": 3,
				"customFilter": "[{\"ref\":\"IS\",\"fieldName\":\"f_shape\",\"val\":\"NOT NULL\"}]",
				"regionCode": "522300",
				"sortParam": "f_committime desc nulls last"
			}
		}
		
		alltbs1 = httpOp(gtyinfo1)['rows']

		last = []

		for tb1 in alltbs1:
			last.append({
				'f_bsm': tb1['f_bsm'],
				'f_xzqmc1': tb1['f_xzqmc1'],
				'f_reject_status': tb1['f_reject_status'],
				'f_review_stagev': tb1['f_review_stage'],
				'f_status': tb1['f_status']
			})

		alltbs2 = httpOp(gtyinfo2)['rows']
		for tb2 in alltbs2:
			last.append({
				'f_bsm': tb2['f_bsm'],
				'f_xzqmc1': tb2['f_xzqmc1'],
				'f_reject_status': tb2['f_reject_status'],
				'f_review_stagev': tb2['f_review_stage'],
				'f_status': tb2['f_status']
			})

		today = str(datetime.date.today() - datetime.timedelta(days=0)).replace('-','_')
		oid = str(uuid.uuid4()).split('-')[0]
		fileoid = today + '_' + oid
	
		lastdt = pandas.DataFrame(last)
		lastdt.to_excel('就地恢复国土调查云拍照举证情况_{}.xlsx'.format(fileoid), sheet_name='工作表1', index=False)
		pandas.io.sql.to_sql(lastdt, '就地恢复国土调查云拍照举证情况_{}'.format(fileoid), engine, index= False, schema='public', if_exists='append')
		
		sql = """with t1 as (select f_xzqmc1,count(*) as zsc from {} group by f_xzqmc1),
							t2 as (select f_xzqmc1,count(*) as wpz from {} where f_reject_status is null group by f_xzqmc1),
							t3 as (select t1.*,t2.wpz from t1 left join t2 on t1.f_xzqmc1 = t2.f_xzqmc1),
							t4 as (select f_xzqmc1,zsc,(case when wpz is null then 0 else wpz end) as wpz from t3),
							t5 as (select f_xzqmc1,zsc,wpz,(zsc-wpz) as ypz from t4),
							t6 as (select f_xzqmc1,cast(zsc as numeric) as zsc, cast(wpz as numeric) as wpz, cast(ypz as numeric) as ypz from t5),
							t7 as (select f_xzqmc1,zsc,wpz,ypz,(ypz/zsc) as bl from t6),
							t8 as (select '黔西南' as f_xzqmc1,sum(zsc) as zsc,sum(wpz) as wpz,sum(ypz) as ypz,(sum(ypz)/sum(zsc)) as bl from t7)
							select * from t8 union select * from t7 order by zsc;""".format('就地恢复国土调查云拍照举证情况_{}'.format(fileoid), '就地恢复国土调查云拍照举证情况_{}'.format(fileoid))
		
		dd = [[i[0]] + [float(d) for d in i[1:]] for i in execute_sql(db, sql, True)]
		dd.insert(0, ['区县名称','总上传图斑','未拍照举证','已拍照举证', '已拍照举证占比'])
		pandas.DataFrame(dd).to_excel('就地恢复国土调查云拍照举证情况统计_{}.xlsx'.format(fileoid), sheet_name='工作表1', index=False)
		
	except Exception as err:
		print('err: ', err)

if __name__ == '__main__':
	time1 = datetime.datetime.now()
	print('start time: ', time1)
	main()
	time2 = datetime.datetime.now()
	print('end time: ', time2)
	print('total time: ', time2 - time1)